import pandas as pd
import tools

pd.set_option('display.unicode.east_asian_width', True)

base_path = r"D:\100-Project\2024-2025-1学期安排\武昌首义学院\29101303_《通信数据运维技术实训（企业）》\报告"
docx_dir = base_path + r"\提交"
excel_file = base_path + r"\成绩提取.xlsx"

filenames = tools.get_filenames(docx_dir)

def calc_score(date):
    match date:
        case '1223':
            return 50
        case '1225':
            return 40
        case '1226':
            return 40
        case '1227':
            return 30
        case '1228':
            return 30
        case '1230':
            return 20
        case '0103':
            return 10

names = []
scores = []
for filename in filenames:
    name, date = tools.truncate_name_date(filename)
    score = calc_score(date)
    names.append(name)
    scores.append(score)

df = pd.DataFrame(list(zip(names, scores)), columns=['姓名', '成绩'])
df.fillna(0, inplace=True)

# -------------------------------------------------------------------------


df0 = pd.read_excel(base_path + r"\《数据运维实训》报告提交记录.xlsx")
df0 = df0[['学号', '姓名']]

df3 = pd.merge(df0, df, how='outer')
df3.fillna(0, inplace=True)
df3 = df3.sort_values(by='学号', ignore_index=True)
print(df3)

df3.to_excel(r'D:\tmp_1.xlsx', index=False)

